package de.ingo.app.mensa.v0_2.helperclasses;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Simple reminder database access helper class. 
 * Defines the basic CRUD operations (Create, Read, Update, Delete)
 * 
 * @author ingorichter
 */
public class DbAdapter {

	//
	// Database Related Constants
	//
	private static final String DATABASE_NAME	= "mensa.db";
    private static final String DATABASE_TABLE	= "Mensa";
    private static final int DATABASE_VERSION	= 1;

	public static final String KEY_DAY 			= "menuDay";
    public static final String KEY_DATE 		= "menuDate";
    public static final String KEY_NAME 		= "menuName";
    public static final String KEY_MENUNUMBER	= "menuNumber";
    public static final String KEY_ROWID		= "_id";
    public static final String KEY_PRICE		= "menuPrice";
    
    private static final String infoTAG 		= "TESTING_APP_DB";
    
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    
    /**
     * Database creation SQL statement from Alt Naundorf
     */
    private static final String DATABASE_CREATE =
            "CREATE TABLE " + DATABASE_TABLE + " ("
            		+ KEY_ROWID + " integer, "//primary key autoincrement, "
                    + KEY_DAY + " text not null, " 
                    + KEY_DATE + " text not null, " 
                    + KEY_NAME + " text not null, "
                    + KEY_MENUNUMBER + " integer, "
                    + KEY_PRICE + " text not null);"; 

    private final Context mCtx;
    
    
    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     * 
     * @author ingorichter
     */
    public DbAdapter(Context ctx) {
    	this.mCtx = ctx;
    }    
    
    
    private static class DatabaseHelper extends SQLiteOpenHelper {
	    DatabaseHelper(Context context) {
	    	super(context, DATABASE_NAME, null, DATABASE_VERSION);
	    	}
	
	    @Override
	    public void onCreate(SQLiteDatabase db) {
	        Log.d(infoTAG, "SDbA | createDB");
	        db.execSQL(DATABASE_CREATE);//"create table menuTable (date TEXT, day TEXT, name TEXT, number INTEGER, flag INTEGER);"); //
	    }
	
	    @Override
	    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	        Log.w(infoTAG, "SDbA | Upgrading database from version " + oldVersion + " to "
	                + newVersion + ", which will destroy all old data");
	        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
	        onCreate(db);
	    }
    }     
    
    
	/**
	 * Open the database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an initialization call)
	 * 
	 * @throws SQLException if the database could be neither opened or created
	 * 
	 * @author ingorichter
	 */
	public DbAdapter open() throws android.database.SQLException {
        Log.d(infoTAG, "SDbA | openDb()");
	    mDbHelper = new DatabaseHelper(mCtx);
	    mDb = mDbHelper.getWritableDatabase();
	    return this;
	}
	
	public void close() {
	    mDbHelper.close();
	}	
	
	
    /**
     * Return a Cursor over the list of all menus in the database
     * 
     * @return Cursor over all menus
     * 
     * @author ingorichter
     */
    public Cursor fetchAllMenus() {
        Log.d(infoTAG, "SDbA | fetchAllMenus()");
                
        return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_DAY, KEY_DATE, KEY_NAME, KEY_MENUNUMBER, KEY_PRICE}, null, null, null, null, null);
    }
		
    
    /**
     * Return a Cursor positioned at the menu that matches the given date
     * 
     * @param menuDate of menu to retrieve
     * 
     * @return Cursor positioned to matching menu, if found
     * 
     * @throws SQLException if menu could not be found/retrieved
     * 
     * @author ingorichter
     */
    public Cursor fetchOneMenu(String menuDay, String menuDbString) throws SQLException {
        Log.d(infoTAG, "SDbA | fetchMenu() | date: " + menuDay);
  
        Cursor mCursor;
        
        if (menuDbString.equalsIgnoreCase("all")) {
        	mCursor =
                mDb.rawQuery(	"SELECT * FROM " + 
                				DATABASE_TABLE + 
                				" WHERE " + 
                				KEY_DAY + 
                				" = '" + 
                				menuDay + 
                				"'", 
                				null); 
        } else {
        
	        mCursor =
	                mDb.rawQuery(	"SELECT * FROM " + 
	                				DATABASE_TABLE + 
	                				" WHERE " + 
	                				KEY_DAY + 
	                				" = '" + 
	                				menuDay + 
	                				"' AND ( " +
	                				menuDbString +
	                				" )", 
	                				null); 
        }
        
        Log.d(infoTAG, "SDbA | Cursor is set");

        if (mCursor != null) {
            mCursor.moveToFirst();
            Log.d(infoTAG, "SDbA | Cursor is not null and now in first position");
        }        
        return mCursor;
    }	
    
    
    /**
     * Create a new menu using the name, date, day, number and flag provided. 
     * 
     * @param menuName the name of the menu like a short desciption
     * @param menuDate the date of the menu as a "dd.mm.yyyy" String
     * @param menuDay the day of the menu as String "Montag"...
     * @param menuNumber the number of the menu sorted by different menus per day
     * @param menuPrice simply the cost of the menu as String
     * 
     * @return Long which is the unique identifier of the row that was inserted 
     * 
     * @author ingorichter
     */
    public long insertMenuRecord(int id, 
    							 String menuDay, 
    							 String menuDate, 
    							 String menuName, 
    							 int menuNumber, 
    							 String menuPrice) {
//        Log.d(infoTAG, "insertMenuRecord() | Infos from param | menu: " + menu + " | day: " + day + "| date: " + date + "| number: " + number);    	
    	ContentValues initialValues = new ContentValues();
    	initialValues.put(KEY_ROWID, id);
        initialValues.put(KEY_DAY, menuDay);
        initialValues.put(KEY_DATE, menuDate);
        initialValues.put(KEY_NAME, menuName);
        initialValues.put(KEY_MENUNUMBER, menuNumber);
        initialValues.put(KEY_PRICE, menuPrice);
//        Log.d(infoTAG, "insertMenuRecord() | Infos from contValues | menu: " + initialValues.get(KEY_NAME) + " | day: " + initialValues.get(KEY_DAY) + "| date: " + initialValues.get(KEY_DATE));
        
        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }

    
    /**
     * Delete the menu with the given date
     * 
     * @param menuDate of menu to delete
     * 
     * @return true if deleted, false otherwise
     * 
     * @author ingorichter
     */
    public boolean deleteMenu(String menuDate) {
        Log.d(infoTAG, "SDbA | deleteMenu()");
        
        return mDb.delete(DATABASE_TABLE, KEY_DATE + "=" + menuDate, null) > 0;
    }
    
    
    /**
     * Deletes the whole DB
     * 
     * @author ingorichter
     */
    public void deleteAll() {
        mDb.delete(DATABASE_TABLE, null, null);
    }
    
    
	/**
	 * "getter"-artige Methoden die einzelnen Spalten aus dem Cursor zu erhalten
	 * 
	 * @author ingorichter
	 */
    public String getMenuDay(Cursor c) {
		return(c.getString(2));
	}
 
    public String getMenuDate(Cursor c) {
		return(c.getString(1));
	}
		
	public String getMenuName(Cursor c) {
		return(c.getString(3));
	}
	
	public int getMenuNumber(Cursor c) {
		return(c.getInt(4));
	}
	
	public String getMenuPrice(Cursor c) {
		return(c.getString(5));
	}
}
